package mammuthus.web.service;

import com.alibaba.fastjson.JSONObject;
import com.webdm.common.tools.NumberUtil;
import com.webdm.common.tools.StringUtil;
import com.webdm.common.tools.TimeUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * @author fbj
 * @version 2013-12-26 下午2:27:03
 */
@Service
public class DbService {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public long getConfNum(int confType, String runType, String filter) {

        List<Object> params = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder("select count(1)  " +
                " from t_confs a " +
                " where 1 = 1 ");

        getQueryConfSqlAndParams(sql, params, confType, runType, filter, 0, 0);


        return jdbcTemplate.queryForLong(sql.toString(), params.toArray());

    }

    public List<Object[]> getConfTypes() {

        List<Map<String, Object>> list = jdbcTemplate.queryForList("select CONCAT(id) id, name  from t_catch_conf_type order by id");
        if (list != null && list.size() > 0) {

            List<Object[]> result = new ArrayList<Object[]>(list.size() * 2);
            for (Map<String, Object> l : list) {

                result.add(new Object[]{
                        l.get("id"),
                        l.get("name")
                });
            }
            return result;
        }
        return null;
    }

    public void getQueryConfSqlAndParams(StringBuilder sql, List<Object> params, int confType, String runType, String filter, int pageNum, int pageSize) {

//	String sql = "select b.name conf_type, conf_name, conf_author, add_time, note, conf,run_type, status  " + 
//                " from t_confs a , t_catch_conf_type b " +
//		  " where conf_type = b.id  ";

        if (confType != -999) {

            sql.append(" and conf_type = ? ");
            params.add(confType);
        }

        if (runType != null && !"".equals(runType) && !"-999".equals(runType)) {

            sql.append(" and run_type = ? ");
            params.add(runType);
        }

        if (filter != null && !"".equals(filter)) {

            sql.append(" and ( conf_name like ? or note like ? or conf like ? or conf_author like ?) ");
            String filterKey = "%" + filter + "%";
            params.add(filterKey);
            params.add(filterKey);
            params.add(filterKey);
            params.add(filterKey);
        }

        if (pageNum > 0 && pageSize > 0) {
            sql.append(" order by a.id desc limit ? offset ?  ");
            params.add(pageSize);
            params.add((pageNum - 1) * pageSize);
        }

    }

    public List<Object[]> getConf(int pageNum, int pageSize, int confType, String runType, String filter) {

        List<Object> params = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder("select a.id id, b.name conf_type, conf_name, conf_author, add_time, note, conf,run_type, status  " +
                " from t_confs a , t_catch_conf_type b " +
                " where conf_type = b.id  ");

        getQueryConfSqlAndParams(sql, params, confType, runType, filter, pageNum, pageSize);


        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), params.toArray());
        Map<String, StringBuilder> relationMap = new HashMap<String, StringBuilder>();
        if (list != null && list.size() > 0) {

            //获取配置的订阅关系

            //取到所有的id
            Set<Integer> set = new HashSet<Integer>();
            for (Map<String, Object> l : list) {

                set.add(NumberUtil.getIntValue(l.get("id")));
            }
            List<Map<String, Object>> relations = jdbcTemplate.queryForList("select distinct catch_conf_id, catch_type_id from t_catch_conf_post_relaction where catch_conf_id in (" + StringUtil.ArrtoString(set.toArray(), false) + ")");


            //找出每个配置的对应type列表，并放到map里面

            if (relations != null && relations.size() > 0) {

                StringBuilder types = null;
                String id = null;
                for (Map<String, Object> relation : relations) {

                    id = StringUtil.toString(relation.get("catch_conf_id"));
                    types = relationMap.get(id);

                    if (types == null) {
                        types = new StringBuilder();
                        relationMap.put(id, types);
                    }

                    types.append("," + relation.get("catch_type_id") + ",");
                }
            }


            List<Object[]> result = new ArrayList<Object[]>(200);
            JSONObject jsonObject = null;
            String actionName = null;

            String curTypes;
            StringBuilder curTypesBuilder;
            for (Map<String, Object> l : list) {

                try {
                    jsonObject = JSONObject.parseObject(l.get("conf").toString());
                } catch (Exception e) {
                    e.printStackTrace();
                    jsonObject = null;
                }

                if (jsonObject != null) {

                    actionName = jsonObject.getString("actionName");
                } else {
                    actionName = "web";
                }


                curTypesBuilder = relationMap.get(StringUtil.toString(l.get("id")));

                if (curTypesBuilder != null) {
                    curTypes = curTypesBuilder.toString();
                } else {
                    curTypes = StringUtil.NULL_STRING;
                    ;

                }
                result.add(new Object[]{
                        l.get("conf_name"),
                        l.get("conf_author"),

                        l.get("note"),
                        l.get("add_time"),
                        actionName,
                        l.get("run_type"),
                        l.get("status"),
                        l.get("conf_type"),
                        curTypes,
                        l.get("id")
                });
            }
            return result;
        }
        return null;
    }

    /**
     * 得到有效配置列表
     */
    public List<Object[]> getValidConf(int pageNum, int pageSize, String filter, boolean isLike) {

        List<Object> params = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder("select id, conf_name, conf, note from t_confs where run_type != 'permanent' " +
                "and status = 'enable'");
        if (filter != null && !"".equals(filter)) {
            if (isLike) {
                sql.append(" and conf_name like ? ");
                String filterKey = "%" + filter + "%";
                params.add(filterKey);
            } else {
                sql.append(" and conf_name = ? ");
                params.add(filter);
            }
        }


        if (pageNum > 0 && pageSize > 0) {
            sql.append(" order by id desc limit ? offset ?  ");
            params.add(pageSize);
            params.add((pageNum - 1) * pageSize);
        }
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), params.toArray());
        if (list != null && list.size() > 0) {

            List<Object[]> result = new ArrayList<Object[]>(200);
            JSONObject jsonObject = null;
            String actionName = null;

            for (Map<String, Object> l : list) {

                try {
                    jsonObject = JSONObject.parseObject(l.get("conf").toString());
                } catch (Exception e) {
                    e.printStackTrace();
                    jsonObject = null;
                }

                if (jsonObject != null) {

                    actionName = jsonObject.getString("actionName");
                } else {
                    actionName = "web";
                }
                result.add(new Object[]{
                        l.get("conf_name"),
                        null,
                        l.get("note"),
                        null,
                        actionName,
                        null,
                        null,
                        null,
                        null,
                        l.get("id")
                });
            }
            return result;
        }
        return null;
    }

    /**
     * 得到有效配置总数
     */
    public long getVaildConfNum(String filter, boolean isLike) {

        List<Object> params = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder("select count(1) from t_confs where run_type != 'permanent' " +
                "and status = 'enable'");

        if (filter != null && !"".equals(filter)) {
            if (isLike) {
                sql.append(" and conf_name like ? ");
                String filterKey = "%" + filter + "%";
                params.add(filterKey);
            } else {
                sql.append(" and conf_name = ? ");
                params.add(filter);
            }
        }
        return jdbcTemplate.queryForLong(sql.toString(), params.toArray());
    }

    public Map<String, Object> get(String confName) {

        List<Map<String, Object>> list = jdbcTemplate.queryForList("select *  from t_confs where conf_name = ?", new String[]{confName});
        if (list != null && list.size() > 0) {

            return list.get(0);
        }
        return null;
    }

    public boolean ifExist(String confName) {

        int all = jdbcTemplate.queryForInt("select count(1) from t_confs where conf_name = ?", new String[]{confName});
        if (all > 0) {
            return true;
        } else {

            return false;
        }
    }

    public void delete(String confName) {

        if (confName != null && !confName.isEmpty())
            jdbcTemplate.update("delete from t_confs where conf_name = ?", new String[]{confName});

    }

    public void updateCatchConf(String confName, JSONObject conf) {

//	delete(confName);
//	save(confName, conf);

        int confType = -1;
        if (conf.containsKey("confType")) {
            confType = NumberUtil.getIntValue(conf.get("confType"));
        }
        jdbcTemplate.update("update t_confs set conf = ? , conf_type = ? ,note = ?,conf_author=? where conf_name = ?", new Object[]{
                conf.toJSONString(),
                confType,
                conf.getString("note"),
                conf.getString("confAuthor"),
                confName
        });

    }

    public String[] getNewsSummaryInfo(long max) {

        List<Map<String, Object>> result = jdbcTemplate.queryForList("select count(1) c,sum(case when b.conf_type = -1 then 1 else 0 end ) d_q, sum(case when b.conf_type = 1 then 1 else 0 end ) d_d, sum(case when b.conf_type = 2 then 1 else 0 end ) d_y from t_news a, t_confs b where a.id > ? and a.catch_id = b.id      ", max);

        if (result != null && result.size() > 0) {

            // List<DayInfo> dayInfos = new ArrayList<DbService.DayInfo>();

//	    DayInfo dayInfo;
//	    	
//	    
//		Map<String, Object> cur = result.get(0);
//		dayInfo = new DayInfo();
//		dayInfo.setC(NumberUtil.getLongValue(cur.get("c")));
//		dayInfo.setD(StringUtil.toString(cur.get("d")));
//		dayInfo.setVc(NumberUtil.getLongValue(cur.get("w_c")));
//		dayInfo.setBc(NumberUtil.getLongValue(cur.get("v_c")));
//		
//	    
//	    return dayInfo;
            Map<String, Object> cur = result.get(0);
            return new String[]{null, StringUtil.toString(cur.get("c")), StringUtil.toString(cur.get("d_d")), StringUtil.toString(cur.get("d_y")), StringUtil.toString(cur.get("d_q"))};

        }
        return null;
    }

    public List<String[]> getNewsDaySummaryInfo(long max) {

        List<Map<String, Object>> result = jdbcTemplate.queryForList("select date(create_at) as d,count(1) c, sum(case when b.conf_type = -1 then 1 else 0 end ) d_q, sum(case when b.conf_type = 1 then 1 else 0 end ) d_d, sum(case when b.conf_type = 2 then 1 else 0 end ) d_y from t_news a, t_confs b where a.id > ? and a.catch_id = b.id   and  create_at >= ?  group by d  order by d ",
                new Object[]{max, TimeUtil.getDate(TimeUtil.getSpedayStand(-10))}
        );

        if (result != null && result.size() > 0) {

            List<String[]> dayInfos = new ArrayList<String[]>();

            DayInfo dayInfo;

            for (Map<String, Object> cur : result) {

//		dayInfo = new DayInfo();
//		dayInfo.setC(NumberUtil.getLongValue(cur.get("c")));
//		dayInfo.setD(StringUtil.toString(cur.get("d")));
//		dayInfo.setVc(NumberUtil.getLongValue(cur.get("w_c")));
//		dayInfo.setBc(NumberUtil.getLongValue(cur.get("b_c")));
                dayInfos.add(new String[]{StringUtil.toString(cur.get("d")), StringUtil.toString(cur.get("c")), StringUtil.toString(cur.get("d_d")), StringUtil.toString(cur.get("d_y")), StringUtil.toString(cur.get("d_q"))});
            }
            return dayInfos;
        }
        return null;
    }

    public List<String[]> getNewsDayDetailSummaryInfo(String date, long max) {

        if (date == null || !date.matches("^[\\d\\-]+$")) {
            date = TimeUtil.getDayStand();
        }
        List<Map<String, Object>> result = jdbcTemplate.queryForList("select hour(create_at) as d,count(1) c, sum(case when b.conf_type = -1 then 1 else 0 end ) d_q, sum(case when b.conf_type = 1 then 1 else 0 end ) d_d, sum(case when b.conf_type = 2 then 1 else 0 end ) d_y from t_news a, t_confs b where a.id > ? and  a.catch_id = b.id  and  create_at >= ? group by d  order by d  "
                , new Object[]{max, date}
        );

        if (result != null && result.size() > 0) {

            List<String[]> dayInfos = new ArrayList<String[]>();

            DayInfo dayInfo;

            for (Map<String, Object> cur : result) {

//		dayInfo = new DayInfo();
//		dayInfo.setC(NumberUtil.getLongValue(cur.get("c")));
//		dayInfo.setD(StringUtil.toString(cur.get("d")));
//		dayInfo.setVc(NumberUtil.getLongValue(cur.get("w_c")));
//		dayInfo.setBc(NumberUtil.getLongValue(cur.get("b_c")));
//		dayInfos.add(dayInfo);
                dayInfos.add(new String[]{StringUtil.toString(cur.get("d")), StringUtil.toString(cur.get("c")), StringUtil.toString(cur.get("d_d")), StringUtil.toString(cur.get("d_y")), StringUtil.toString(cur.get("d_q"))});

            }
            return dayInfos;
        }
        return null;
    }


//public NewsInfo getNews(String  uniqKey){
//
//
//   	List<Map<String, Object>> result = jdbcTemplate.queryForList("select * from t_news where unique_key = ? ",
//   		uniqKey
//   	);
//
//   	if(result != null && result.size() > 0){
//
//   	    return getANews(result.get(0), null);
//   	}
//
//   	return null;
//       }
//    public NewsInfo getNews(int id){
//
//
//   	List<Map<String, Object>> result = jdbcTemplate.queryForList("select * from t_news where id = ? ",
//   		 id
//   	);
//
//   	if(result != null && result.size() > 0){
//
//   	    return getANews(result.get(0), null);
//   	}
//
//   	return null;
//       }
//    public List<NewsInfo> getNews(int pageSize, int pageNum){
//
//	if(pageNum <= 0){
//	    pageNum = 1;
//	}
//	if(pageSize < 20){
//	    pageSize = 20;
//	}
//	List<Map<String, Object>> result = jdbcTemplate.queryForList("select * from (select * from t_news order by id desc limit 1000) c   order by id desc limit ? offset ?  ",
//		 new Object[]{pageSize , (pageNum - 1 ) * pageSize}
//	);
//
//	if(result != null && result.size() > 0){
//
//
//	    List<Map<String, Object>> l = jdbcTemplate.queryForList("select id, name from t_catch_conf_type");
//
//	    Map<String, String> mapping = new HashMap<String, String>();
//	    if(l != null && l.size() > 0){
//
//		for(Map<String, Object> entry : l){
//
//		    mapping.put(StringUtil.toString(entry.get("id")), StringUtil.toString(entry.get("name")));
//		}
//	    }
//
//	    List<NewsInfo> newInfos = new ArrayList<NewsInfo>();
//
//	    NewsInfo newsInfo;
//
//	    for(Map<String, Object> cur : result){
//
//		newsInfo = getANews(cur, mapping);
//		newInfos.add(newsInfo);
//	    }
//	    return newInfos;
//	}
//
//	return null;
//    }
//
//    private NewsInfo getANews(Map<String, Object> cur, Map<String, String> typeNameMapping){
//
//	NewsInfo newsInfo = new NewsInfo();
//	newsInfo.setId(NumberUtil.getIntValue(cur.get("id")));
//	newsInfo.setTitle(StringUtil.toString(cur.get("title")));
//	newsInfo.setUrl(StringUtil.toString(cur.get("url")));
//	newsInfo.setUnique_key(StringUtil.toString(cur.get("unique_key")));
//	newsInfo.setBody(StringUtil.toString(cur.get("body")));
//	newsInfo.setUser_name(StringUtil.toString(cur.get("user_name")));
//	newsInfo.setUser_tag(StringUtil.toString(cur.get("user_tag")));
//	newsInfo.setSystem_tag(StringUtil.toString(cur.get("system_tag")));
//	newsInfo.setCreate_at((Date)(cur.get("create_at")));
//
//	String typeIds = StringUtil.toString(cur.get("type_ids"));
//	if(typeNameMapping != null && typeIds != null && !",".equals(typeIds)){
//
//	    	List<String> ids = CatchUtil.getValues(typeIds, "-?\\d+", 0);
//		List<String> idNames = new ArrayList<String>();
//
//		if(ids != null && ids.size() > 0){
//
//		    StringBuilder r = new StringBuilder();
//		    String name = null;
//		    for(String id : ids){
//
//			name = typeNameMapping.get(id);
//			if(name == null || name.isEmpty()){
//			    idNames.add(id);
//			}else{
//			    idNames.add(name);
//			}
//
//		    }
//		}
//
//		newsInfo.setTypeNames(idNames);
//	}
//
//	newsInfo.setCatch_id( StringUtil.toString(cur.get("catch_id")));
//	return newsInfo;
//    }

    public long getNewsTotalNum2() {

        return jdbcTemplate.queryForInt("select id from  t_news order by id desc limit 1  ");
    }

    public long getNewsTotalNum() {

        return jdbcTemplate.queryForInt("select count(1) from (select * from t_news order by id desc limit 1000) c ");
    }


    public void save(String confName, JSONObject conf) {

        String confAuthor = conf.getString("confAuthor");
        if (confAuthor == null) {
            confAuthor = "-";
        }
        jdbcTemplate.update("insert into t_confs(conf_name, conf, add_time, conf_author,note) values(?, ?, ?, ?,?)", new Object[]{
                confName,
                conf.toJSONString(),
                new Date(),
                confAuthor,
                conf.getString("note")

        });
    }


    public void changeConfStatus(String confName, String status) {


        jdbcTemplate.update("update  t_confs set status = ? where conf_name = ? ", new Object[]{
                status, confName
        });
    }

    public void changeConfRunType(String confName, String type) {


        jdbcTemplate.update("update  t_confs set run_type = ? where conf_name = ? ", new Object[]{
                type, confName
        });
    }


    public void addSearchKeyword(String keyword, String urls) {


        int count = jdbcTemplate.queryForInt("select count(1) from t_search_key_word where keyword = ?", keyword);
        if (count >= 1) {

            jdbcTemplate.update("update t_search_key_word set urls = ? ,  status = ? ,  update_time = ? where keyword = ?", new Object[]{
                    urls, 0, new Date(), keyword});
        } else {
            jdbcTemplate.update("insert into t_search_key_word(keyword, urls, status, add_time, update_time) values(?, ?, ?, ?, ?)", new Object[]{
                    keyword, urls, 0, new Date(), new Date()});
        }
    }

    public void deleteSearchKeyword(String keyword) {


        if (keyword != null && !keyword.isEmpty())
            jdbcTemplate.update("delete from  t_search_key_word  where keyword = ?", new Object[]{
                    keyword});

    }

    /**
     * 添加抓取配置和订阅者的对应关系
     *
     * @author fbj
     * @version 2014-3-1 上午11:44:48
     */
    public void addCatchConfAndCatchTypePostRelation(int catchConfId, int catchTypeId) {

        int all = jdbcTemplate.queryForInt("select count(1) a from t_catch_conf_post_relaction where catch_conf_id = ? and  catch_type_id = ?", new Object[]{catchConfId, catchTypeId});
        if (all == 0)
            jdbcTemplate.update("insert into t_catch_conf_post_relaction(catch_conf_id, catch_type_id, insert_time) values(?, ?, ?)", new Object[]{catchConfId, catchTypeId, new Date()});
    }

    /**
     * 删除抓取配置和订阅者的对应关系
     *
     * @author fbj
     * @version 2014-3-1 上午11:44:48
     */
    public void deleteCatchConfAndCatchTypePostRelation(int catchConfId, int catchTypeId) {

        jdbcTemplate.update("delete from  t_catch_conf_post_relaction where catch_conf_id = ? and  catch_type_id = ? ", new Object[]{catchConfId, catchTypeId});
    }

    public void addOpenCatchConf(String name, String conf) {

        jdbcTemplate.update("insert into t_conf_open_catch(name, conf, status) values(?, ?, ?)", new Object[]{name, conf, "add"});

    }

//    public List<OpenCatchConfBean> getOpenCatchConfs(){
//
//        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from t_conf_open_catch order by id limit 100");
//
//        if(list != null && list.size() > 0){
//
//            List<OpenCatchConfBean> result = new ArrayList<OpenCatchConfBean>(100);
//
//            OpenCatchConfBean cur = null;
//
//            for(Map<String, Object> l : list){
//
//                cur = new OpenCatchConfBean(l);
//                result.add(cur);
//            }
//
//            return result;
//
//        }
//
//        return null;
//
//    }
//
//    public List<OpenCatchResultBean> getOpenCatchResult(String name){
//
//        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from t_open_catch_result where name = ? order by id desc limit 100" , name);
//
//        if(list != null && list.size() > 0){
//
//            List<OpenCatchResultBean> result = new ArrayList<OpenCatchResultBean>(100);
//
//            OpenCatchResultBean cur = null;
//
//            for(Map<String, Object> l : list){
//
//                cur = new OpenCatchResultBean(l);
//                result.add(cur);
//            }
//
//            return result;
//
//        }
//
//        return null;
//
//    }
//
//    public List<OpenCatchResultCountBean> getOpenCatchResultCount(){
//
//
//        List<Map<String, Object>> list = jdbcTemplate.queryForList("select  name, count(1) num from t_open_catch_result group by name order by name");
//
//        if(list != null && list.size() > 0){
//
//            List<OpenCatchResultCountBean> result = new ArrayList<OpenCatchResultCountBean>(100);
//
//            OpenCatchResultCountBean cur = null;
//
//            for(Map<String, Object> l : list){
//
//                cur = new OpenCatchResultCountBean(l);
//                result.add(cur);
//            }
//
//            return result;
//
//        }
//
//        return null;
//
//    }

    public String getOpenCatchConfStatus(int id) {

        List<Map<String, Object>> list = jdbcTemplate.queryForList("select status from t_conf_open_catch where id = ? ", id);

        if (list != null && list.size() > 0) {

            return StringUtil.toString(list.get(0).get("status"));
        }
        return null;
    }

    public void delOpenCatchConfStatus(int id) {

        jdbcTemplate.update("delete  from t_conf_open_catch where id = ? ", id);


    }

    public static class DayInfo {

        private String d;
        private long c, vc, bc;

        public String getD() {
            return d;
        }

        public void setD(String d) {
            this.d = d;
        }

        public long getC() {
            return c;
        }

        public void setC(long c) {
            this.c = c;
        }

        public long getVc() {
            return vc;
        }

        public void setVc(long vc) {
            this.vc = vc;
        }

        public long getBc() {
            return bc;
        }

        public void setBc(long bc) {
            this.bc = bc;
        }


    }
}
    
//    public static class NewsInfo extends News{
//
//	private int id;
//
//	private List<String> typeNames;
//	public int getId() {
//	    return id;
//	}
//
//	public void setId(int id) {
//	    this.id = id;
//	}
//
//	public List<String> getTypeNames() {
//	    return typeNames;
//	}
//
//	public void setTypeNames(List<String> typeNames) {
//	    this.typeNames = typeNames;
//	}
//
//
//
//
//
//
//    }
//}
